﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using MxWeiXinPF.Common;

using System.Data.SqlClient;
using MxWeiXinPF.DBUtility;
using MxWeiXinPF.Model;
using Aspose.Cells;

namespace MxWeiXinPF.Web.admin.order
{
    public partial class order_total_wxpay : MxWeiXinPF.Web.UI.ManagePage
    {
        protected MxWeiXinPF.Model.manager admin_info;
        protected int express_status;
        protected string keywords = string.Empty;
        protected int page;
        protected int pageSize;
        protected int payment_status;
        protected int status;
        protected int totalCount;
        protected string starttime;
        protected string endtime;
        protected int selectshopid;
        protected decimal sumamount = 0.0m;
        protected int shopid = 0;       
       
        protected string CombSqlTxt(int _status, int _payment_status, int _express_status, string _keywords)
        {
            StringBuilder builder = new StringBuilder();

            builder.Append(" and o.status=3" );
            builder.Append(" and o.payment_status=2");
            builder.Append(" and o.express_status=2");
            builder.Append(" and o.payment_id=3 ");

            if (this.selectshopid == 0) //店铺全选状态
            {
                Model.manager weixin = base.GetAdminInfo();
                if (weixin.agentLevel == 0)  //普通店铺
                {
                    int tempshopid = 0;
                    if (GetRelativeAdminInfo() != null)
                    {
                        tempshopid = GetRelativeAdminInfo().id;
                    }
                    else
                    {
                        tempshopid = GetAdminInfo().id;
                    }

                    if (weixin.id == 39 || tempshopid == 39) //管理员
                    {

                    }
                    else //普通店铺
                    {
                        builder.AppendFormat(" and o.shopid =" + tempshopid);
                    }
                }
                else //代理店铺
                {

                    builder.Append(" and o.shopid in (");
                    foreach (ListItem x in this.ddlShops.Items)
                    {
                        if (x.Value != "0")
                        {
                            builder.Append(x.Value);
                            builder.Append(",");
                        }
                    }
                    builder = builder.Remove(builder.Length - 1, 1);
                    builder.Append(") ");
                    // strTemp.AppendFormat(" shopid =" + selectshopid);
                }
            }
            else
            {
                builder.AppendFormat(" and o.shopid =" + selectshopid);
            }

            if (string.IsNullOrEmpty(starttime))
            {
                String startdate = DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + "01";

                builder.AppendFormat(" and add_time >= '" + startdate + "' ");
            }
            else
            {
                builder.AppendFormat(" and add_time >= '" + starttime + "' ");
            }
            if (string.IsNullOrEmpty(endtime))
            {
                builder.AppendFormat(" and add_time <= '" + DateTime.Now.AddDays(1).ToShortDateString() + "' ");
            }
            else
            {
                builder.AppendFormat(" and add_time <= '" + endtime + "' ");
            }
            return builder.ToString();
        }

        protected void ddlShops_SelectedIndexChanged(object sender, EventArgs e)
        {
            base.Response.Redirect(Utils.CombUrlTxt("order_total_wxpay.aspx", "selectshopid={0}&start={1}&end={2}",
                this.ddlShops.SelectedValue, starttime, endtime));
        }
      
        private int GetPageSize(int _default_size)
        {
            int num;
            if (int.TryParse(Utils.GetCookie("order_list_wxpay_page_size"), out num) && (num > 0))
            {
                return num;
            }
            return _default_size;
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            this.pageSize = this.GetPageSize(10);
            this.selectshopid = MXRequest.GetQueryInt("selectshopid");
            this.starttime = MXRequest.GetQueryString("start");
            this.endtime = MXRequest.GetQueryString("end");

            if (!this.Page.IsPostBack)
            {
                base.ChkAdminLevel("order_list_wxpay", MXEnums.ActionEnum.View.ToString());
                this.admin_info = base.GetAdminInfo();
                this.TreeBind();
                this.RptBind(" " + this.CombSqlTxt(this.status, this.payment_status, this.express_status, this.keywords), "add_time desc");

                if (string.IsNullOrEmpty(this.starttime))
                {
                    //txtStart.Text = DateTime.Now.AddDays(-7).ToShortDateString();
                    txtStart.Text = DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + "01";
                }
                else
                {
                    this.txtStart.Text = starttime;
                }
                if (string.IsNullOrEmpty(endtime))
                {
                    txtEnd.Text = DateTime.Now.ToShortDateString();
                }
                else
                {
                    this.txtEnd.Text = endtime;
                }
            }
        }

        public DataSet GetListEx(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(string.Concat(new object[] { "select o.order_no, o.add_time, (select top 1 real_name from dt_manager m where m.id=o.shopid and m.role_id<>18 ) as shopName , b.sku, a.goods_title, b.spec, b.Factory, b.costprice as costprice, a.market_price,a.sale_price,a.quantity,a.sale_price*a.quantity as sumprice from dt_orders o inner join dt_order_goods a on o.id = a.order_id inner join wx_shop_product b on a.goods_id = b.id" }));
            builder.Append(" where o.shopid!=" + 0);
            if (strWhere.Trim() != "")
            {
                builder.Append("  " + strWhere);
            }            

            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(builder.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, builder.ToString(), filedOrder));
        }

        public DataSet GetListSum(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(string.Concat(new object[] { " select sum(o.real_amount) from dt_orders o " }));
            builder.Append(" where o.shopid<>" + 0);
            if (strWhere.Trim() != "")
            {
                builder.Append(" " + strWhere);
            }
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetListByCounty(int wid, int county, int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(string.Concat(new object[] { "select o.order_no, o.add_time, (select top 1 real_name from dt_manager m where m.id=o.shopid and m.role_id<>18 ) as shopName , b.sku, a.goods_title, b.spec, b.Factory, b.costprice as costprice, a.sale_price,a.sale_price, a.quantity,a.sale_price*a.quantity as sumprice from dt_orders o inner join dt_order_goods a on o.id = a.order_id inner join wx_shop_product b on a.goods_id = b.id" }));
            builder.Append(string.Concat(new object[]{
                " where o.shopid in (select a.id from dt_manager as a where a.agentLevel <= 1 and a.role_id<>18 and a.county=",county,
                ") "
            }));
            if (strWhere.Trim() != "")
            {
                builder.Append("   " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(builder.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, builder.ToString(), filedOrder));
        }

        public DataSet GetListByCountySum(int wid, int county, string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(string.Concat(new object[] { " select sum(o.real_amount) from dt_orders o " }));
            builder.Append(string.Concat(new object[]{
                " where o.shopid in (select a.id from dt_manager as a where a.agentLevel <= 1 and a.county=",county,
                ") "
            }));
            if (strWhere.Trim() != "")
            {
                builder.Append("   " + strWhere);
            }
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetListByCity(int wid, int city, int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(string.Concat(new object[] { "select o.order_no, o.add_time, (select top 1 real_name from dt_manager m where m.id=o.shopid and m.role_id<>18 ) as shopName , b.sku, a.goods_title, b.spec, b.Factory, b.costprice as costprice, a.sale_price,a.cost_price, a.quantity,a.sale_price*a.quantity as sumprice from dt_orders o inner join dt_order_goods a on o.id = a.order_id inner join wx_shop_product b on a.goods_id = b.id" }));
            builder.Append(string.Concat(new object[]{
                " where o.shopid in (select a.id from dt_manager as a where a.agentLevel <= 2 and a.role_id<>18 and a.city=",city,
                ") "
            }));
            if (strWhere.Trim() != "")
            {
                builder.Append("    " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(builder.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, builder.ToString(), filedOrder));
        }

        public DataSet GetListByCitySum(int wid, int city, string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(string.Concat(new object[] { " select sum(o.real_amount) from dt_orders o " }));
            builder.Append(string.Concat(new object[]{
                " where o.shopid in (select a.id from dt_manager as a where a.agentLevel <= 2 and a.city=",city,
                ") "
            }));
            if (strWhere.Trim() != "")
            {
                builder.Append("   " + strWhere);
            }
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetListByProvince(int wid, int provice, int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(string.Concat(new object[] { "select o.order_no, o.add_time, (select top 1 real_name from dt_manager m where m.id=o.shopid and m.role_id<>18 ) as shopName , b.sku, a.goods_title, b.spec, b.Factory, b.costprice as costprice, a.sale_price,a.cost_price, a.quantity,a.sale_price*a.quantity as sumprice from dt_orders o inner join dt_order_goods a on o.id = a.order_id inner join wx_shop_product b on a.goods_id = b.id" }));
            builder.Append(string.Concat(new object[]{
                " where o.shopid in (select a.id from dt_manager as a where a.agentLevel <= 3 and a.role_id<>18 and a.province=",provice,
                ") "
            }));
            if (strWhere.Trim() != "")
            {
                builder.Append("   " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(builder.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, builder.ToString(), filedOrder));
        }

        public DataSet GetListByProvinceSum(int wid, int provice, string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(string.Concat(new object[] { " select sum(o.real_amount) from dt_orders o " }));
            builder.Append(string.Concat(new object[]{
                " where o.shopid in (select a.id from dt_manager as a where a.agentLevel <= 3 and a.province=",provice,
                ") "
            }));
            if (strWhere.Trim() != "")
            {
                builder.Append("   " + strWhere);
            }
            return DbHelperSQL.Query(builder.ToString());
        }

        private void RptBind(string _strWhere, string _orderby)
        {
            MxWeiXinPF.Model.manager weixin = base.GetAdminInfo();
            this.page = MXRequest.GetQueryInt("page", 1);

            if (this.selectshopid > 0)
            {
                this.ddlShops.SelectedValue = this.selectshopid.ToString();
            }
            else
            {
                this.ddlShops.SelectedValue = "0";
            }

            //支付为己完成，状态为己完成
            DataSet set = new DataSet();
            DataSet ds2 = new DataSet();

            if (weixin.agentLevel == 0)
            {
                if (weixin.id == 39)
                {
                    set = GetListEx(this.pageSize, this.page, _strWhere, _orderby, out this.totalCount);
                    ds2 = GetListSum(_strWhere);
                }
                else
                {
                    set = GetListEx(this.pageSize, this.page, _strWhere, _orderby, out this.totalCount);
                    ds2 = GetListSum(_strWhere);
                }
            }
            else
            {
                if (weixin.agentLevel == 1)
                {
                    set = GetListByCounty(weixin.id, string.IsNullOrEmpty(weixin.county) ? 0 : int.Parse(weixin.county), this.pageSize, this.page, _strWhere, _orderby, out this.totalCount);
                    ds2 = GetListByCountySum(0, string.IsNullOrEmpty(weixin.county) ? 0 : int.Parse(weixin.county), _strWhere);
                }
                else if (weixin.agentLevel == 2)
                {
                    set = GetListByCity(weixin.id, string.IsNullOrEmpty(weixin.city) ? 0 : int.Parse(weixin.city), this.pageSize, this.page, _strWhere, _orderby, out this.totalCount);
                    ds2 = GetListByCitySum(0, string.IsNullOrEmpty(weixin.city) ? 0 : int.Parse(weixin.city), _strWhere);
                }
                else if (weixin.agentLevel == 3)
                {
                    set = GetListByProvince(weixin.id, string.IsNullOrEmpty(weixin.province) ? 0 : int.Parse(weixin.province), this.pageSize, this.page, _strWhere, _orderby, out this.totalCount);
                    ds2 = GetListByProvinceSum(0, string.IsNullOrEmpty(weixin.province) ? 0 : int.Parse(weixin.province), _strWhere);
                }
                else
                {
                    return;
                }
            }
            
            this.rptList.DataSource = set;
            this.rptList.DataBind();

            this.txtPageNum.Text = this.pageSize.ToString();
            string linkUrl = Utils.CombUrlTxt("order_total_wxpay.aspx", "selectshopid={0}&start={1}&end={2}", new string[] { this.selectshopid.ToString(), starttime, endtime });
            this.PageContent.InnerHtml = Utils.OutPageList(this.pageSize, this.page, this.totalCount, linkUrl, 8);

            if (ds2 != null && ds2.Tables.Count > 0 && ds2.Tables[0].Rows.Count > 0)
            {
                if (ds2.Tables[0].Rows[0][0] != null && ds2.Tables[0].Rows[0][0].ToString() != "")
                {
                    sumamount = Convert.ToDecimal(ds2.Tables[0].Rows[0][0].ToString());
                    this.lbSum.Text = sumamount.ToString() + "元";
                    return;
                }
            }
            this.lbSum.Text = sumamount.ToString() + "元";
        }

        protected void txtPageNum_TextChanged(object sender, EventArgs e)
        {
            int num;
            if (int.TryParse(this.txtPageNum.Text.Trim(), out num) && (num > 0))
            {
                Utils.WriteCookie("order_list_wxpay_page_size", num.ToString(), 0x3840);
            }
            base.Response.Redirect(Utils.CombUrlTxt("order_total_wxpay.aspx", "selectshopid={0}&start={1}&end={2}", new string[] { this.selectshopid.ToString(), starttime, endtime }));
        }

        private void TreeBind()
        {
            try
            {
                Model.manager weixin = base.GetAdminInfo();
                BLL.manager bll = new BLL.manager();
                DataSet ds = new DataSet();

                //weixin.agentLevel = 1;
                //Response.Write(weixin.agentLevel);

                if (weixin.agentLevel == 0)
                {
                    if (weixin.id == 39)
                    {
                        ds = bll.GetList(-1, "", " id asc");
                    }
                    else
                    {
                        ds = bll.GetList(-1, " id = " + weixin.id + " ", " id asc");
                    }
                }
                else
                {
                    if (weixin.agentLevel == 1)
                    {
                        ds = bll.GetListByCounty(string.IsNullOrEmpty(weixin.county) ? 0 : int.Parse(weixin.county));
                    }
                    else if (weixin.agentLevel == 2)
                    {
                        ds = bll.GetListByCity(string.IsNullOrEmpty(weixin.city) ? 0 : int.Parse(weixin.city));
                    }
                    else if (weixin.agentLevel == 3)
                    {
                        ds = bll.GetListByProvince(string.IsNullOrEmpty(weixin.province) ? 0 : int.Parse(weixin.province));
                    }
                    else
                    {
                        return;
                    }
                }

                this.ddlShops.Items.Clear();
                this.ddlShops.Items.Add(new ListItem("所有店铺", "0"));
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    ddlShops.Items.Add(new ListItem(ds.Tables[0].Rows[i]["real_name"].ToString(), ds.Tables[0].Rows[i]["id"].ToString()));

                    if (ds.Tables[0].Rows[i]["id"].ToString() == this.selectshopid.ToString())
                    {
                        this.ddlShops.SelectedValue = ds.Tables[0].Rows[i]["id"].ToString();
                    }
                }
            }
            catch (Exception ex)
            {

            }
        }
        
        protected void btnSearch_Click(object sender, EventArgs e)
        {
            starttime = txtStart.Text;
            endtime = txtEnd.Text;
            base.Response.Redirect(Utils.CombUrlTxt("order_total_wxpay.aspx", "selectshopid={0}&start={1}&end={2}", new string[] { this.selectshopid.ToString(), starttime, endtime }));
        }

        private DataSet RptBindImport(string _strWhere, string _orderby)
        {
            MxWeiXinPF.Model.manager weixin = base.GetAdminInfo();
            this.page = MXRequest.GetQueryInt("page", 1);

            //返回的记录数
            int totalPageSize = 1000000;

            //支付为己完成，状态为己完成
            DataSet set = new DataSet();            
            if (weixin.agentLevel == 0)
            {
                if (weixin.id == 39)
                {
                    set = GetListEx(totalPageSize, this.page, _strWhere, _orderby, out this.totalCount);                   
                }
                else
                {
                    set = GetListEx(totalPageSize, this.page, _strWhere, _orderby, out this.totalCount);                    
                }
            }
            else
            {
                if (weixin.agentLevel == 1)
                {
                    set = GetListByCounty(weixin.id, string.IsNullOrEmpty(weixin.county) ? 0 : int.Parse(weixin.county), totalPageSize, this.page, _strWhere, _orderby, out this.totalCount);                    
                }
                else if (weixin.agentLevel == 2)
                {
                    set = GetListByCity(weixin.id, string.IsNullOrEmpty(weixin.city) ? 0 : int.Parse(weixin.city), totalPageSize, this.page, _strWhere, _orderby, out this.totalCount);                    
                }
                else if (weixin.agentLevel == 3)
                {
                    set = GetListByProvince(weixin.id, string.IsNullOrEmpty(weixin.province) ? 0 : int.Parse(weixin.province), totalPageSize, this.page, _strWhere, _orderby, out this.totalCount);                    
                }                
            }

            //set = GetListEx(totalPageSize, this.page, _strWhere, _orderby, out this.totalCount);
            //this.rptList.DataSource = set;
            //this.rptList.DataBind();
            return set;

        }

        protected void lbtnExport_Click(object sender, EventArgs e)
        {
            try
            {
                if (this.admin_info != null)
                {
                    this.shopid = this.admin_info.id;
                }
                else
                {
                    this.admin_info = base.GetAdminInfo();
                    this.shopid = this.admin_info.id;
                }

                Workbook workbook = new Workbook(base.Server.MapPath("../../upload/xlstep/shoporder.xls"));
                int count = workbook.Worksheets.Count;
                Worksheet sheet = workbook.Worksheets[0];
                Cell cell = sheet.Cells[0, 0];

                DataSet ds = this.RptBindImport(" " + this.CombSqlTxt(this.status, this.payment_status, this.express_status, this.keywords), "add_time desc");
                DataTable dt = ds.Tables[0];
                if ((dt != null) && (dt.Rows.Count > 0))
                {
                    //添加统计的销售总金额，总成本
                    double totalSalePrice = 0, totalCostPrice = 0;
                    foreach (DataRow row in dt.Rows)
                    {
                        totalSalePrice += Convert.ToDouble(row["sale_price"].ToString()) * Convert.ToInt32(row["quantity"].ToString());
                        totalCostPrice += Convert.ToDouble(row["CostPrice"].ToString()) * Convert.ToInt32(row["quantity"].ToString()); 
                    }

                    this.FillTableOld(sheet, dt, totalSalePrice, totalCostPrice);
                    workbook.RemoveMacro();
                    XlsSaveOptions saveOptions = new XlsSaveOptions
                    {
                        SaveFormat = SaveFormat.Excel97To2003
                    };
                    string s = this.shopid.ToString() + "-" + DateTime.Now.ToString("yyyy-MM-dd") +  "订单统计表.xls";
                    workbook.Save(base.Response, base.Server.UrlEncode(s), ContentDisposition.Attachment, saveOptions);
                }
                else
                {
                    this.Page.ClientScript.RegisterStartupScript(base.GetType(), "asd", "<script>alert('没有符合条件的记录！');</script>");
                }

            }
            catch (Exception ex)
            {

            }
        }

        protected void FillTableOld(Worksheet sheet, DataTable dt, double totalSalePrice, double totalCostPrice)
        {
            int num = 1;
            int num2 = 1;
            foreach (DataRow row in dt.Rows)
            {
                sheet.Cells[num2, 0].PutValue(dt.Rows[num - 1][0].ToString());
                sheet.Cells[num2, 1].PutValue(dt.Rows[num - 1][1].ToString());
                sheet.Cells[num2, 2].PutValue(dt.Rows[num - 1][2].ToString());
                sheet.Cells[num2, 3].PutValue(dt.Rows[num - 1][3].ToString());
                sheet.Cells[num2, 4].PutValue(dt.Rows[num - 1][4].ToString());
                sheet.Cells[num2, 5].PutValue(dt.Rows[num - 1][5].ToString());
                sheet.Cells[num2, 6].PutValue(dt.Rows[num - 1][6].ToString());
                sheet.Cells[num2, 7].PutValue(dt.Rows[num - 1][7].ToString());
                sheet.Cells[num2, 8].PutValue(dt.Rows[num - 1][8].ToString());
                sheet.Cells[num2, 9].PutValue(dt.Rows[num - 1][9].ToString());
                sheet.Cells[num2, 10].PutValue(dt.Rows[num - 1][10].ToString());               
                num++;
                num2++;
            }

            sheet.Cells[num2, 0].PutValue("总成本价：");
            sheet.Cells[num2, 1].PutValue( totalCostPrice );
            sheet.Cells[num2, 2].PutValue("总销售价：");
            sheet.Cells[num2, 3].PutValue(totalSalePrice);
            sheet.Cells[num2, 4].PutValue("总利润：");
            sheet.Cells[num2, 5].PutValue(( totalSalePrice - totalCostPrice ));
        }

    }
}